﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using TravelSite.Entities;
using Dapper;
using System.Data;

namespace TravelSite.DAL.Repositories
{
    public class ContractRepository : BaseRepository<Contract>
    {
        protected override string TableName
        {
            get { return "[Contract]"; }
        }

        public IEnumerable<Contract> Search(int? agentIdAgent, int? agentIdAdmin, string status, string contactName, string packageStatus)
        {
            return Connection.Query<Contract>("spContract_Search @AgentIdAgent, @AgentIdAdmin, @Status, @ContactName, @PackageStatus",
                new { 
                    AgentIdAgent = agentIdAgent,
                    AgentIdAdmin = agentIdAdmin, 
                    Status = (status == "ALL" ? null : status), 
                    ContactName = (string.IsNullOrEmpty(contactName) ? null : contactName),
                    PackageStatus = (packageStatus == "ALL" ? null : packageStatus),
                });
        }

        public IEnumerable<Contract> FindByContact(int contactId)
        {
            return Connection.Query<Contract>(
                @"  SELECT ctr.ContractId, ctr.ContractDate, ctr.ContactId, ctr.TemplateId, ctr.Status, ctr.AgentId,
                        ct.FirstName + ' ' + ct.LastName as ContactName,
                        ctr.ViewDate, ctr.ViewIP, ctr.SignedDate, ctr.SignedIP,
                        tp.VacationLength, cp.[Status] as PackageStatus
                    FROM [Contract] (nolock) ctr left join Contact (nolock) as ct on ctr.ContactId = ct.ContactId
                                                left join TravelPackage (nolock) as tp on ctr.PackageId = tp.PackageId
									            left join ClientPackage (nolock) as cp on ctr.TravelId = cp.TravelId
                    WHERE ctr.ContactId = @contactId", new { contactId = contactId });
        }

        public override Contract Find(int contractId)
        {
            return Connection.Query<Contract>(
                @"  SELECT ctr.ContractId, ctr.ContractDate, ctr.ContactId, ctr.TemplateId, ctr.Status, ctr.AgentId, 
                        isnull(ctr.PackageId, -1) as PackageId, ctr.ExpDate,
                        ctr.CardNumber, ctr.CardDate, ctr.CardCVV, ctr.CardCC,
                        ct.FirstName + ' ' + ct.LastName as ContactName,
                        u.FirstName + ' ' + u.LastName as AgentName,
                        ctr.ExpDate, cp.TravelId, cp.[Status] as PackageStatus
                    FROM [Contract] (nolock) ctr left join Contact (nolock) as ct on ctr.ContactId = ct.ContactId
                                                left join [User] (nolock) as u on ctr.AgentId = u.Id
                                                left join ClientPackage (nolock) as cp on ctr.TravelId = cp.TravelId
                    WHERE ctr.ContractId = @contractId", new { contractId = contractId }).SingleOrDefault();
        }

        public Contract Find(string linkCode)
        {
            return Connection.Query<Contract>(
                @"  SELECT ctr.ContractId, ctr.ContractDate, ctr.ContactId, ctr.TemplateId, ctr.Status, ctr.AgentId, isnull(ctr.PackageId, -1) as PackageId,
                        ctr.CardNumber, ctr.CardDate, ctr.CardCVV, ctr.CardCC,
                        ct.FirstName + ' ' + ct.LastName as ContactName
                    FROM [Contract] (nolock) ctr left join Contact (nolock) as ct on ctr.ContactId = ct.ContactId
                    WHERE ctr.LinkCode = @LinkCode", new { LinkCode = linkCode }).SingleOrDefault();
        }
        
        public IEnumerable<ContractField> ContractFields(int? templateId, int? contractId, int? agentId, int? contactId)
        {
            return Connection.Query<ContractField>("spContractField_List @TemplateId, @AgentId, @ContactId, @ContractId ", new { TemplateId = templateId, AgentId = agentId, ContactId = contactId, ContractId = contractId });
        }

        public IEnumerable<Template> ContractTemplate()
        {
            return Connection.Query<Template>(@"select TemplateId, FileName from Template (nolock) where IsEmail = 0");
        }

        public void Edit(Contract contract, string arrTemplateFieldId, string arrContactColumnName, string arrContactColumnValue)
        {
            int result = Connection.Query<int>(
                @"spContractEdit @ContractId, @ContactId, @TemplateId, @Status, @AgentId, @PackageId, @ExpDate,
                            @CardNumber, @CardDate, @CardCVV, @CardCC,
                            @TemplateFieldId, @ContactColumnName, @ContactColumnValue",

                new
                {
                    ContractId = contract.ContractId,
                    ContactId = contract.ContactId,
                    TemplateId = contract.TemplateId,
                    Status = contract.Status,
                    AgentId = contract.AgentId,
                    PackageId = contract.PackageId,
                    ExpDate = contract.ExpDate,
                    CardNumber = contract.CardNumber,
                    CardDate = contract.CardDate,
                    CardCVV = contract.CardCVV,
                    CardCC = contract.CardCC,
                    
                    TemplateFieldId = arrTemplateFieldId,
                    ContactColumnName = arrContactColumnName,
                    ContactColumnValue = arrContactColumnValue
                }).Single();
            contract.ContactId = result;
        }

        public string SetStatus(int contractId, string status, string ip, int? contractLinkValidDays, int? contactId = null)
        {
            string result = "";

            var p = new DynamicParameters();
            p.Add("ContractId", contractId);
            p.Add("Status", status);
            p.Add("ContractLinkValidDays", contractLinkValidDays);
            p.Add("IP", ip);
            p.Add("Pin", dbType: DbType.String, direction: ParameterDirection.Output, size: 4);

            Connection.Execute(@"spContract_SetStatus @ContractId = @ContractId, @Status = @Status, @ContractLinkValidDays = @ContractLinkValidDays, 
                @IP = @IP, @Pin = @Pin out", p);

            if (status.ToLower() == "signed")
            {
                result = Connection.Query<string>("select Pin from Contact (nolock) where ContactId = " + contactId.Value.ToString()).Single();
            }
            else
            {
                result = p.Get<string>("Pin");
            }
            return result;
        }

        public string LinkCode(int contractId)
        {
            return Connection.Query<string>(string.Format("select dbo.fnContract_LinkCode({0}) as LinkCode", contractId.ToString())).Single();
        }

        public override void Delete(int id)
        {
            Connection.Execute(@"spContractDelete @ContractId", new { ContractId = id });
        }

        public void SetBooked(int contractId, string choiceIdList, string overrideChoiceList)
        {
            Connection.Execute(@"spContract_SetBooked @ContractId, @ChoiceIdList, @OverrideChoiceList", new { ContractId = contractId, ChoiceIdList = choiceIdList, OverrideChoiceList = overrideChoiceList });
        }

        public void SetClear(int contractId)
        {
            Connection.Execute(@"spContract_SetClear @ContractId", new { ContractId = contractId });
        }

        public void SetCanceled(int contractId)
        {
            Connection.Execute(@"spContract_SetCanceled @ContractId", new { ContractId = contractId });
        }

        public IEnumerable<TravelPackage> FindPackageForContract(int contactId, int contractId)
        {
            string strSQL = @"select tp.PackageId, tp.Cost, tp.PackageName, tp.VacationLength
                    from [TravelPackage] (nolock) as tp";
                    /*where not exists(select 1 from ClientPackage (nolock) as cp where tp.PackageId = cp.PackageId and cp.ClientId = @ClientId)
                        or exists(select 1 from [Contract] (nolock) as ct where tp.PackageId = ct.PackageId and ct.ContractId = @ContractId)";*/

            IEnumerable<TravelPackage> result = Connection.Query<TravelPackage>(strSQL, new { ClientId = contactId, ContractId = contractId });
            return result;
        }
    }
}
